﻿using AutoMapper;
using Dapper;
using King.Data.Model;
using King.Helper;
using Microsoft.AspNetCore.Mvc;
using MySqlConnector;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace King.Api.Controllers
{

    [Route("api/[controller]/[action]")]
    [ApiController]
    public class MesQcScreenController : ControllerBase
    {
        private ICacheService _cache;
        private IMapper _mapper;
        //private string ConnString = "Server=61.183.71.118;Port=9306;DataBase=nclev5;uid=root;pwd=123456;";
        private string ConnString = string.Empty;
        /// <summary>
        /// 
        /// </summary>
        /// <param name="mapper"></param>
        /// <param name="cache"></param>
        public MesQcScreenController(IMapper mapper, ICacheService cache)
        {
            _cache = cache;
            _mapper = mapper;
        }

        /// <summary>
        /// 过去10天一次下线合格率
        /// </summary>       
        /// <returns></returns>
        [HttpGet]
        public List<OnePassRate> GetOnePassRateInfo()
        {

            List<OnePassRate> onePassRateList = new List<OnePassRate>();

            //过去10天 A\B\C线每日检验总数
            string qcSql = string.Format(@"SELECT t.uda5c,t.dd,t.dd, count(*) num FROM nclev5.uqcmchkrecord t
                                            where 
                                             t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                            group by t.uda5c,t.dd
                                            order by t.dd asc");

            //过去10天 A\B\C线每日下线箱总量
            string totalSql = string.Format(@" SELECT t.uda5c,t.dd,sum(t.qty) num FROM nclev5.xxcl t
                                            where 
                                             t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                             group by t.uda5c,t.dd
                                             order by t.dd desc");

            //过去10天各

            List<LineKinds> lineList = new List<LineKinds>();
            using (IDbConnection connection = new MySqlConnection(ConnString))
            {
                //获取总量信息
                var totalQtyInfo = connection.Query<QcOnePassRate>(totalSql).ToList();

                var checkQcInfo = connection.Query<QcOnePassRate>(qcSql).ToList();

                //获取总部合格
                foreach (var item in totalQtyInfo)
                {
                    OnePassRate model = new OnePassRate();
                    model.CreateDate = item.Dd;

                    LineKinds lineKinesModel = new LineKinds();
                    lineKinesModel.LineName = item.Uda5c;

                    var numsInfo = checkQcInfo.Where(x => x.Uda5c == item.Uda5c && x.Dd == item.Dd).FirstOrDefault();
                    if (numsInfo != null)
                    {
                        lineKinesModel.PassRate = (item.Num - numsInfo.Num) / item.Num;
                    }
                    else
                    {
                        continue;
                    }
                    lineList.Add(lineKinesModel);
                    model.LineTypeList = lineList;
                    onePassRateList.Add(model);
                }


            }
            return onePassRateList;

        }

        /// <summary>
        /// 过去10天出箱口漏光漏水率
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public List<OnePassRate> GetLeakWaterRateInfo()
        {
            List<OnePassRate> onePassRateList = new List<OnePassRate>();
            //过去10天各

            List<LineKinds> lineList = new List<LineKinds>();
            //过去10天 A\B\C线每日检验总数
            string qcSql = string.Format(@"SELECT t.uda5c,t.dd, count(*) num FROM nclev5.uqcmchkrecord t
                                            where   (t.qescode='OPQ0112' or t.qescode='OPQ0113') and
                                             t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                            group by t.uda5c,t.dd
                                            order by t.dd asc");

            //过去10天 A\B\C线每日下线箱总量
            string totalSql = string.Format(@" SELECT t.uda5c,t.dd,sum(t.qty) num FROM nclev5.xxcl t
                                            where  
                                             t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                             group by t.uda5c,t.dd
                                             order by t.dd desc");

            using (IDbConnection connection = new MySqlConnection(ConnString))
            {
                //获取总量信息
                var totalQtyInfo = connection.Query<QcOnePassRate>(totalSql).ToList();

                var checkQcInfo = connection.Query<QcOnePassRate>(qcSql).ToList();

                //获取总部合格
                foreach (var item in totalQtyInfo)
                {
                    OnePassRate model = new OnePassRate();
                    model.CreateDate = item.Dd;

                    LineKinds lineKinesModel = new LineKinds();
                    lineKinesModel.LineName = item.Uda5c;

                    var numsInfo = checkQcInfo.Where(x => x.Uda5c == item.Uda5c && x.Dd == item.Dd).FirstOrDefault();
                    if (numsInfo != null)
                    {
                        lineKinesModel.PassRate = (item.Num - numsInfo.Num) / item.Num;
                    }
                    else
                    {
                        continue;
                    }
                    lineList.Add(lineKinesModel);
                    model.LineTypeList = lineList;
                    onePassRateList.Add(model);
                }

            }

            return onePassRateList;

        }


        /// <summary>
        /// 过去10天不良率占比
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public List<DefectiveRatePersentRate> GetDefectiveRatePersent()
        {

            List<DefectiveRatePersentRate> defectiveRatePersentRateList = new List<DefectiveRatePersentRate>();
            //过去10天各

            List<DefectiveDetail> defectiveDetailList = new List<DefectiveDetail>();
            //前一天 A\B\C线每日检验总数
            string qcSql = string.Format(@"SELECT t.uda5c,t.qesname, count(*) num FROM nclev5.uqcmchkrecord t
                                            where 
                                             t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                            group by t.uda5c,t.qesname
                                            order by t.uda5c asc");

            //前一天 A\B\C线每日下线箱总量
            string totalSql = string.Format(@" SELECT t.uda5c,sum(t.qty) num FROM nclev5.xxcl t
                                            where 
                                             t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                             group by t.uda5c");

            using (IDbConnection connection = new MySqlConnection(ConnString))
            {
                //获取总量信息
                var totalQtyInfo = connection.Query<QcOnePassRate>(totalSql).ToList();

                var checkQcInfo = connection.Query<QcOnePassRate>(qcSql).ToList();

                //获取总不合格

                foreach (var item in totalQtyInfo)
                {
                    DefectiveRatePersentRate model = new DefectiveRatePersentRate();
                    model.LineName = item.Uda5c;

                    var detailInfo = checkQcInfo.Where(x => x.Uda5c == item.Uda5c).ToList();
                    foreach (var items in detailInfo)
                    {



                        var dModel = new DefectiveDetail()
                        {
                            QesName = items.QesName,
                            PersentRate = items.Num / item.Num

                        };
                        defectiveDetailList.Add(dModel);
                    }

                    model.DefectiveDetailList = defectiveDetailList;
                    defectiveRatePersentRateList.Add(model);
                }

            }

            return defectiveRatePersentRateList;
        }


        /// <summary>
        /// 获取昨日产量
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public List<YieldPerDay> GetYieldPerDay()
        {
            string totalSql = string.Format(@" SELECT t.uda3, t.uda5c,sum(t.qty) qty FROM nclev5.xxcl t
                                            where t.dd >= DATE_ADD(CURDATE(), INTERVAL -20 DAY) 
                                             group by t.uda5c,t.uda3
                                             order by t.uda3 desc");
            using (IDbConnection connection = new MySqlConnection(ConnString))
            {

                return connection.Query<YieldPerDay>(totalSql).ToList();

            }

        }


        /// <summary>
        /// 昨日各部件不良占比
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public List<PartPersent> GetPartPersentInfo()
        {
            string totalSql = string.Format(@" SELECT t.name,count(*) rate FROM nclev5.uqcmchkrecord t
                                                 where t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                                group by t.name");
            string sql = string.Format(@"SELECT sum(t.qty) qty  FROM nclev5.xxcl t
                                        where 
                                         t.dd >= DATE_ADD(CURDATE(), INTERVAL -20 DAY) ");
            List<PartPersent> list = new List<PartPersent>();
            using (IDbConnection connection = new MySqlConnection(ConnString))
            {

                var totalInfo = connection.Query<float>(sql).FirstOrDefault();
                if (totalInfo > 0)
                {
                    var partInfo = connection.Query<PartPersent>(totalSql).ToList();
                    foreach (var item in partInfo)
                    {
                        var model = new PartPersent()
                        {
                            Name = item.Name,
                            Rate = item.Rate / totalInfo
                        };
                        list.Add(model);
                    }
                }


            }
            return list;

        }

        /// <summary>
        /// 获取过去10天漏光漏水
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public List<OnePassRate> GetLeakWaterQty()
        {

            List<OnePassRate> onePassRateList = new List<OnePassRate>();
            //过去10天各

            List<LineKinds> lineList = new List<LineKinds>();
            //过去10天 A\B\C线每日检验总数
            string qcSql = string.Format(@"SELECT t.uda5c,t.dd, count(*) num FROM nclev5.uqcmchkrecord t
                                            where   (t.qescode='OPQ0112' or t.qescode='OPQ0113') and
                                             t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                            group by t.uda5c,t.dd
                                            order by t.dd asc");

            //过去10天 A\B\C线每日下线箱总量
            string totalSql = string.Format(@" SELECT t.uda5c,t.dd,sum(t.qty) num FROM nclev5.xxcl t
                                            where  
                                             t.dd >= DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
                                             group by t.uda5c,t.dd
                                             order by t.dd desc");

            using (IDbConnection connection = new MySqlConnection(ConnString))
            {
                //获取总量信息
                var totalQtyInfo = connection.Query<QcOnePassRate>(totalSql).ToList();

                var checkQcInfo = connection.Query<QcOnePassRate>(qcSql).ToList();

                //获取总部合格
                foreach (var item in totalQtyInfo)
                {
                    OnePassRate model = new OnePassRate();
                    model.CreateDate = item.Dd;

                    LineKinds lineKinesModel = new LineKinds();
                    lineKinesModel.LineName = item.Uda5c;
                    lineKinesModel.PassRate = item.Num;
                    lineList.Add(lineKinesModel);
                    model.LineTypeList = lineList;
                    onePassRateList.Add(model);
                }

            }

            return onePassRateList;
        }

    }
}
